package com.zbowen.druid.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

@Controller
public class WayInfoController {

    @Autowired
    private DataSource dataSource;

    // 速度最快
    String sql1 = "select record, min(round(duration / 60)) 'duration'\n" +
            "from zbw_way_info where record >= '2023-12-01 07:45:00'  \n" +
            "and date_add(str_to_date(record, '%Y-%m-%d %H:%i:%s'), interval duration SECOND) < '2023-12-01 09:15:00' \n" +
            "group by record ";

    // 路程最短
    String sql2 = "select record, min(round(duration / 60)) 'duration'\n" +
            "from zbw_way_info where  record >= '2023-12-01 07:45:00'  \n" +
            "and date_add(str_to_date(record, '%Y-%m-%d %H:%i:%s'), interval duration SECOND) < '2023-12-01 09:15:00' \n" +
            "and  distance = 12742 \n" +
            "group by record \n";

    // 红绿灯最少
    String sql3 = "select record, min(round(duration / 60)) 'duration'\n" +
            "from zbw_way_info where record >= '2023-12-01 07:45:00'  \n" +
            "and date_add(str_to_date(record, '%Y-%m-%d %H:%i:%s'), interval duration SECOND) < '2023-12-01 09:15:00' \n" +
            "and lights = 7 \n" +
            "group by record ";

    // 高速优先
    String sql4 = "select record, min(round(duration / 60)) 'duration'\n" +
            "from zbw_way_info where record >= '2023-12-01 07:45:00'  \n" +
            "and date_add(str_to_date(record, '%Y-%m-%d %H:%i:%s'), interval duration SECOND) < '2023-12-01 09:15:00' \n" +
            "and tolls > 0 " +
            "group by record ";

    // 西乡大道
    String sql5 = "select record, min(round(duration / 60)) 'duration'\n" +
            "from zbw_way_info where  record >= '2023-12-01 07:45:00'  \n" +
            "and date_add(str_to_date(record, '%Y-%m-%d %H:%i:%s'), interval duration SECOND) < '2023-12-01 09:15:00' \n" +
            "and distance = '13783'\n" +
            "group by record ";




    @RequestMapping("/wayInfo")
    @ResponseBody
    public Map<String, Object> wayInfo(String curDate) throws SQLException, ParseException {

        Map<String, Object> map = new HashMap<>();

        List<String> categories = new ArrayList<>();
        SimpleDateFormat sdf = new SimpleDateFormat("HH:mm");
        Date date = sdf.parse("07:00");
        Calendar calendar = Calendar.getInstance();
        for (int i = 0; i <= 45; i++) {
            String format = sdf.format(date);
            categories.add(format);
            calendar.setTime(date);
            calendar.add(Calendar.MINUTE, 2);
            date = calendar.getTime();
        }

        map.put("categories", categories);

        List<Map<String, Object>> ways = new ArrayList<>();

        map.put("series", ways);


        sql1 = "select round(avg(duration) / 60) 'duration', work_time  from zbw_way_info zwi where \n" +
                "road = '西乡大道 --> 西乡大道辅路 --> 广深公路 --> 深南北环立交桥 --> 北环大道 --> 北环大道出口 --> 北环沙河西立交 --> 沙河西路辅路 --> 沙河西路 --> 沙河西路辅路 --> 白石路辅路 --> 科技南八路'\n" +
                "and  date_add(str_to_date(work_time, '%H:%i:%s'), interval duration SECOND) < '09:20:00'\n" +
                "group by work_time order by work_time";

        sql2 = "select round(avg(duration) / 60) 'duration', work_time  from zbw_way_info zwi where \n" +
                "road = '银田路 --> 宝安大道 --> 深南大道 --> 深南大道辅路 --> 深南科技立交桥 --> 高新南四道 --> 科技南八路'\n" +
                "and  date_add(str_to_date(work_time, '%H:%i:%s'), interval duration SECOND) < '09:20:00'\n" +
                "group by work_time order by work_time";

        sql3 = "select round(avg(duration) / 60) 'duration', work_time  from zbw_way_info zwi where \n" +
                "road = '西乡大道 --> 西乡大道辅路 --> 广深公路 --> 深南大道辅路 --> 深南大道 --> 深南大道辅路 --> 深南科技立交桥 --> 高新南四道 --> 科技南八路'\n" +
                "and  date_add(str_to_date(work_time, '%H:%i:%s'), interval duration SECOND) < '09:20:00'\n" +
                "group by work_time order by work_time";

        sql4 = "select round(avg(duration) / 60) 'duration', work_time  from zbw_way_info zwi where \n" +
                "road = '创业一路 --> 宝安大道 --> 深南大道 --> 深南大道辅路 --> 深南科技立交桥 --> 高新南四道 --> 科技南八路'\n" +
                "and  date_add(str_to_date(work_time, '%H:%i:%s'), interval duration SECOND) < '09:20:00'\n" +
                "group by work_time order by work_time";

        sql5 = "select round(avg(duration) / 60) 'duration', work_time  from zbw_way_info zwi where \n" +
                "road = '银田路 --> 通成路 --> 铁仔山通成路隧道 --> 广深公路 --> 深南大道辅路 --> 深南大道 --> 深南大道辅路 --> 深南科技立交桥 --> 高新南四道 --> 科技南八路'\n" +
                "and  date_add(str_to_date(work_time, '%H:%i:%s'), interval duration SECOND) < '09:20:00'\n" +
                "group by work_time order by work_time";

        String[] sql = new String[]{sql1, sql2, sql3, sql4, sql5};
        String[] name = new String[]{"北环大道", "宝安大道", "广深公路", "新湖路", "通成路"};


        Connection conn = dataSource.getConnection();

        for (int i = 0; i < 3; i++) {
            Map<String, Object> way = new HashMap<>();
            ways.add(way);

            List<Integer> durations = new ArrayList<>();

            PreparedStatement statement = conn.prepareStatement(sql[i]);
            ResultSet rs = statement.executeQuery();

            Map<String, Integer> temp = new HashMap<>();

            while (rs.next()) {
                String work_time = rs.getString("work_time").substring(0, 5);
                int duration = rs.getInt("duration");
                temp.put(work_time, duration);
            }

            for (String category : categories) {
                int duration = temp.get(category) == null ? 45 : temp.get(category);
                durations.add(duration);
            }


            way.put("data", durations);
            way.put("name", name[i]);
        }


        conn.close();
        return map;
    }

    public static void main(String[] args) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("HH:mm");
        Date date = sdf.parse("06:59");

        Calendar calendar = Calendar.getInstance();

        for (int i = 0; i <= 120; i++) {
            calendar.setTime(date);
            calendar.add(Calendar.MINUTE, 1);
            date = calendar.getTime();
            String format = sdf.format(date);
            System.out.println(format);
        }
    }

}
